package cn.jgls.merdoc;

import java.math.BigDecimal;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;

import java.sql.SQLException;
import java.util.List;
import com.ibatis.sqlmap.client.SqlMapClient;
import cn.jgls.merdoc.model.*;

import java.io.FileInputStream;
import java.io.File;

public class MakeDeptSale {
	private BigDecimal dept;

	private String ls_year;

	private String ls_month;

	private String ls_realpath;

	private String ls_plmc;

	private String ls_dept;
	
	
	
	

	public BigDecimal getDept() {
		return dept;
	}

	public void setDept(BigDecimal dept) {
		this.dept = dept;
	}

	public String getYear() {
		return ls_year;
	}

	public void setYear(String ls_year) {
		this.ls_year = ls_year;
	}

	public String getMonth() {
		return ls_month;
	}

	public void setMonth(String ls_month) {
		this.ls_month = ls_month;
	}

	public String getPath() {
		return ls_realpath;
	}

	public void setPath(String ls_realpath) {
		this.ls_realpath = ls_realpath;
	}

	public String getPlmc() {
		return ls_plmc;
	}

	public void setPlmc(String ls_plmc) {
		this.ls_plmc = ls_plmc;
	}

	public String getSDept() {
		return ls_dept;
	}

	public void setSDept(String ls_dept) {
		this.ls_dept = ls_dept;
	}

	private String outputFile = "";

	// public static String outputFile = "test20070212.xls";
	public static String fileToBeRead = "e:\\test.xls";

	public void cExcel() {
		outputFile = ls_realpath + "//xls//" + ls_year + ls_month + ls_dept
				+ ".xls";
		boolean exists = (new File(outputFile)).exists();
		if (exists) {
			// File or directory exists
			// System.out.println("文件已存在...");
            exists = (new File(outputFile)).delete();
            createExcel();
		} else {
			// File or directory does not exist
			// System.out.println("文件不存在...");
			createExcel();
		}

	}

	public void createExcel() {
		short ii;
		HSSFCell cell0;
		HSSFCell cell1;
		HSSFCell cell2;
		HSSFCell cell3;
		HSSFCell cell4;
		HSSFCell cell5;
		HSSFCell cell6;
		HSSFCell cell7;
		HSSFCell cell8;
		HSSFCell cell9;
		HSSFCell cell10;
		HSSFCell cell11;
		HSSFCell cell12;
		
		HSSFRow row;
		try {
			outputFile = ls_realpath + "//xls//" + ls_year + ls_month + ls_dept
					+ ".xls";
			// create a cell style object 创建一个元素的样式对象
			// 创建新的Excel 工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 在Excel工作簿中建一工作表，其名为缺省值
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			HSSFCellStyle cellStyle2 = workbook.createCellStyle();
			HSSFCellStyle cellStyle3 = workbook.createCellStyle();
			// set cell border 设置元素的边框
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			// cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			HSSFFont font1 = workbook.createFont();
			font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			// 此处的文字大小为实际excel中的20倍即可
			font1.setFontHeight((short) 280);
			cellStyle.setFont(font1);

			HSSFSheet sheet = workbook.createSheet();
			// 设置sheet的名字
			workbook.setSheetName(0, ls_plmc, HSSFWorkbook.ENCODING_UTF_16);// 设置中文编码；
			// 在索引0的位置创建行（最顶端的行
			// 第一行，标题
			ii = 0;
			row = sheet.createRow(ii);
			cell0 = row.createCell((short) 0);
			cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
			cell0.setCellValue(ls_year + "年" + ls_month + "月" + ls_plmc
					+ "品类销售明细");
			cell0.setCellStyle(cellStyle);
			cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			// 第二行，表头
			ii++;
			row = sheet.createRow(ii);
			cell0 = row.createCell((short) 0);
			cell1 = row.createCell((short) 1);
			cell2 = row.createCell((short) 2);
			cell3 = row.createCell((short) 3);
			cell4 = row.createCell((short) 4);
			cell5 = row.createCell((short) 5);
			cell6 = row.createCell((short) 6);
			cell7 = row.createCell((short) 7);
			cell8 = row.createCell((short) 8);
			cell9 = row.createCell((short) 9);
			cell10 = row.createCell((short) 10);
//			cell11 = row.createCell((short) 11);
//			cell12 = row.createCell((short) 12);
			
			cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
			cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
//			cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
//			cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
			
			cell0.setCellValue("新品种号编码");
			cell1.setCellValue("新品种号名称");
			cell2.setCellValue("类编");
			cell3.setCellValue("名称");
			cell4.setCellValue("进价");
			cell5.setCellValue("售价");
			cell6.setCellValue("数量");
			cell7.setCellValue("成本");
			cell8.setCellValue("金额");
			cell9.setCellValue("毛利额");
			cell10.setCellValue("毛利率(%)");
//			cell11.setCellValue("新品种号编码");
//			cell12.setCellValue("新品种号名称");
			cell0.setCellStyle(cellStyle2);
			cell1.setCellStyle(cellStyle2);
			cell2.setCellStyle(cellStyle2);
			cell3.setCellStyle(cellStyle2);
			cell4.setCellStyle(cellStyle2);
			cell5.setCellStyle(cellStyle2);
			cell6.setCellStyle(cellStyle2);
			cell7.setCellStyle(cellStyle2);
			cell8.setCellStyle(cellStyle2);
			cell9.setCellStyle(cellStyle2);
			cell10.setCellStyle(cellStyle2);
//			cell11.setCellStyle(cellStyle2);
//			cell12.setCellStyle(cellStyle2);
			
			// 第三行，数据开始
			cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);
			// 从数据库中取数据
			DeptSale lds_tmp = new DeptSale();
			lds_tmp.setYear(ls_year);
			lds_tmp.setMonth(ls_month);
			Short li_tmp = new Short(ls_dept);
            
			lds_tmp.setDept(li_tmp);
			SqlMapClient sqlMap = MySqlMapConfig.getSqlMapInstance();

			List Data = null;
			if (ls_dept.equals("8")) {
				try {
					Data = sqlMap.queryForList("get08Excel", lds_tmp);
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
			} else {
				try {
                    int li_year= Integer.parseInt(ls_year);
                    if(li_year<2010){
                        Data = sqlMap.queryForList("getDeptExcelOld", lds_tmp);
                    }else{
                        Data = sqlMap.queryForList("getDeptExcel", lds_tmp);
                    }
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
			}
            int li_rowcount=Data.size();
			for (int i = 0; i < li_rowcount; i++) {
				
				ii = setExcelCell(ii, cellStyle3, sheet, Data, i);
			}
			/*
			 * //在索引0的位置创建单元格（左上端 HSSFCell cell = row.createCell((short)0); //
			 * 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //
			 * 在单元格中输入一些内容 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			 * cell.setCellValue("这也是测试"); //下一行 row=sheet.createRow( (short)1);
			 * cell=row.createCell( (short)0); //设置单元格内容 cell.setCellValue(
			 * "Powered by 夏天"); //下一行 row=sheet.createRow( (short)1);
			 * cell=row.createCell( (short)1); //设置单元格内容 cell.setCellValue(
			 * "kkkk by gggg");
			 */
			// 新建一输出文件流
			FileOutputStream fOut = new FileOutputStream(outputFile);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			// System.out.println("文件生成...");
		} catch (Exception e) {
			System.out.println("已运行 xlCreate() : " + e);
		}
	}
/**
 * set excel cell data
 * @param ii
 * @param cellStyle3
 * @param sheet
 * @param Data
 * @param i
 * @return
 */
	private short setExcelCell(short ii, HSSFCellStyle cellStyle3, HSSFSheet sheet, List Data, int i) {
		HSSFCell cell0;
		HSSFCell cell1;
		HSSFCell cell2;
		HSSFCell cell3;
		HSSFCell cell4;
		HSSFCell cell5;
		HSSFCell cell6;
		HSSFCell cell7;
		HSSFCell cell8;
		HSSFCell cell9;
		HSSFCell cell10;
		HSSFCell cell11;
		HSSFCell cell12;
		
		HSSFRow row;
		String ls_lb;
		String ls_mc;
		String ls_pqbm;
		String ls_pqmc;
		Double ld_jj;
		Double ld_dj;
		Double ld_zsl;
		Double ld_zje;
		Double ld_zjjje;
		Double ld_mle;
		Double ld_mlv;
		String ls_xpzhbm;
		String ls_xpzhmc;
		
		ls_lb = ((DeptSale) Data.get(i)).getLb();
		ls_mc = ((DeptSale) Data.get(i)).getMc();
		ld_jj = ((DeptSale) Data.get(i)).getJj();

		ld_dj = ((DeptSale) Data.get(i)).getDj();

		ld_zsl = ((DeptSale) Data.get(i)).getZsl();

		ld_zje = ((DeptSale) Data.get(i)).getZje();

		ld_zjjje = ((DeptSale) Data.get(i)).getZjjje();

		ld_mle = ((DeptSale) Data.get(i)).getMle();

		ld_mlv = ((DeptSale) Data.get(i)).getMlv();

		ls_pqbm = ((DeptSale) Data.get(i)).getPqbm();
		ls_pqmc = ((DeptSale) Data.get(i)).getPqmc();

		ls_xpzhbm = ((DeptSale) Data.get(i)).getXpzhbm();
		ls_xpzhmc = ((DeptSale) Data.get(i)).getXpzhmc();

		ii++;
		row = sheet.createRow(ii);
		cell0 = row.createCell((short) 0);
		cell1 = row.createCell((short) 1);
		cell2 = row.createCell((short) 2);
		cell3 = row.createCell((short) 3);
		cell4 = row.createCell((short) 4);
		cell5 = row.createCell((short) 5);
		cell6 = row.createCell((short) 6);
		cell7 = row.createCell((short) 7);
		cell8 = row.createCell((short) 8);
		cell9 = row.createCell((short) 9);
		cell10 = row.createCell((short) 10);
//		cell11 = row.createCell((short) 11);
//		cell12 = row.createCell((short) 12);

		cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
		cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
//		cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
//		cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell0.setCellValue(ls_pqbm);
		cell1.setCellValue(ls_pqmc);
		cell2.setCellValue(ls_lb);
		cell3.setCellValue(ls_mc);
		cell4.setCellValue(ld_jj.doubleValue());
		cell5.setCellValue(ld_dj.doubleValue());
		cell6.setCellValue(ld_zsl.doubleValue());
		cell7.setCellValue(ld_zjjje.doubleValue());
		cell8.setCellValue(ld_zje.doubleValue());
		cell9.setCellValue(ld_mle.doubleValue());
		cell10.setCellValue(ld_mlv.doubleValue());
//		cell11.setCellValue(ls_xpzhbm);
//		cell12.setCellValue(ls_xpzhmc);
		
		cell0.setCellStyle(cellStyle3);
		cell1.setCellStyle(cellStyle3);
		cell2.setCellStyle(cellStyle3);
		cell3.setCellStyle(cellStyle3);
		cell4.setCellStyle(cellStyle3);
		cell5.setCellStyle(cellStyle3);
		cell6.setCellStyle(cellStyle3);
		cell7.setCellStyle(cellStyle3);
		cell8.setCellStyle(cellStyle3);
		cell9.setCellStyle(cellStyle3);
		cell10.setCellStyle(cellStyle3);
//		cell11.setCellStyle(cellStyle3);
//		cell12.setCellStyle(cellStyle3);
		return ii;
	}

	public void readExcel() {
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
					fileToBeRead));
			// System.out
			// .println("===SheetsNum===" +
			// workbook.getNumberOfSheets());//获取sheet数
			for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {// 不等于空的话。有数据
				if (null != workbook.getSheetAt(numSheets)) {
					HSSFSheet aSheet = workbook.getSheetAt(numSheets); // 获得一个sheet
					for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
							.getLastRowNum(); rowNumOfSheet++) {
						if (null != aSheet.getRow(rowNumOfSheet)) {
							HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
							for (short cellNumOfRow = 0; cellNumOfRow <= aRow
									.getLastCellNum(); cellNumOfRow++) {
								if (null != aRow.getCell(cellNumOfRow)) {
									HSSFCell aCell = aRow.getCell(cellNumOfRow);
									System.out.println(aCell
											.getStringCellValue());
								}
							}
						}
					}
				}
			}
		} catch (Exception e) {
			System.out.println("ReadExcelError" + e);
		}
	}

	public static void main(String[] args) {
		MakeDeptSale poi = new MakeDeptSale();
		poi.createExcel();
		System.out.println("文件已生成");
	}
}
